﻿using _9jCardRegisterCollection.Helper;
using _9jCardRegisterCollection.Models;
using AntdUI;
using CardRegisterCollection.Utility;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.OpenXml4Net.OPC.Internal;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.Streaming.Values;
using Org.BouncyCastle.Utilities;
using SixLabors.Fonts;
using SixLabors.ImageSharp.PixelFormats;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Windows.Input;
using ZXing;
using ZXing.QrCode;
using ZXing.QrCode.Internal;

namespace _9jCardRegisterCollection.Views
{
    public partial class DomesticExportExcel : Form
    {
        public DomesticExportExcel(AntdUI.Window Window, AntList<DomesticAttendeeForTable> domesticAttendee)
        {
            domesticAttendees = domesticAttendee;
            window = Window;
            InitializeComponent();

            dt = new DataTable();
            dt.Columns.Add("序号", System.Type.GetType("System.String"));
            dt.Columns.Add("人员姓名", System.Type.GetType("System.String"));
            dt.Columns.Add("性别", System.Type.GetType("System.String"));
            dt.Columns.Add("身份证件号码", System.Type.GetType("System.String"));
            dt.Columns.Add("工作单位", System.Type.GetType("System.String"));
            dt.Columns.Add("办证类型", System.Type.GetType("System.String"));

            for (int i = 0; i < domesticAttendees.Count; i++)
            {
                DataRow dr = dt.NewRow();   
                dr["序号"] = i.ToString();
                dr["人员姓名"] = domesticAttendees[i].Name.ToString();
                dr["性别"] = domesticAttendees[i].Gender.ToString();
                dr["身份证件号码"] = domesticAttendees[i].IDNumber.ToString();
                dr["工作单位"] = domesticAttendees[i].WorkUnit.ToString();
                dr["办证类型"] = domesticAttendees[i].CardType.ToString();
                dt.Rows.Add(dr);
            }
            if (domesticAttendees.Count > 0)
            {
                Group = domesticAttendees[0].UserDefine1.ToString();
            }
            
        }
        static string Group = string.Empty;
        static DataTable dt;
        AntdUI.Window window;
        AntList<DomesticAttendeeForTable> domesticAttendees;
        string USER_PIC_FOLDER_PATH = string.Empty;
        List<string> list = new List<string>();
        /// <summary>
        /// 验证失败的人员
        /// </summary>
        private int CheckErrorPersonCount = 0;
        private int CheckWarnPersonCount = 0;

        private void btnExport_Click(object sender, EventArgs e)
        {
            
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择数据头像文件夹路径\n1.单张照片小于1.5M  2.命名格式：姓名证件号码.jpg\n3.清晰度需 照片宽度*水平分辨率>24000";

            if (dialog.ShowDialog() == DialogResult.OK)
            {
               
                USER_PIC_FOLDER_PATH = dialog.SelectedPath;
                this.label1.Text = string.Format("您已经【{0}】作为人员数据头像匹配的路径", USER_PIC_FOLDER_PATH);
            }

            if (InitView())
            {
                this.button1.Enabled = true;
            }

        }

        private bool InitView()
        {
            DataTable dtView = dt.Copy();
           
            dtView.Columns.Add("图片", typeof(byte[]));
            dtView.Columns.Add("提示", System.Type.GetType("System.String"));
            var root = new DirectoryInfo(USER_PIC_FOLDER_PATH);
            FileInfo[] files = root.GetFiles("*.jpg");
            if (files.Length == 0)
            {
                MessageBox.Show("【" + USER_PIC_FOLDER_PATH + "】没有jpg照片", "人员头像异常", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            var fileNames = files.Select(_ => Path.GetFileName(_.FullName));
            lvwUsers.Items.Clear();
            this.lvwUsers.BeginUpdate();
            this.lvwUsers.View = View.Details;
            this.lvwUsers.SmallImageList = this.imgUserPic;
            this.lvwUsers.Columns.Add("照片", 100, System.Windows.Forms.HorizontalAlignment.Center);
            this.lvwUsers.Columns.Add("人员姓名", 100, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("身份证件号码", 160, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("对应照片", 180, System.Windows.Forms.HorizontalAlignment.Left);
            this.lvwUsers.Columns.Add("消息", 200, System.Windows.Forms.HorizontalAlignment.Left);

            for (int i = 0; i < dtView.Rows.Count; i++)
            {
                var row = dtView.Rows[i];
                ListViewItem listViewItem = new ListViewItem();
                listViewItem.ImageIndex = i;
                listViewItem.SubItems.Add(row["人员姓名"].ToString());
                listViewItem.SubItems.Add(row["身份证件号码"].ToString());
                listViewItem.Tag = i;

                var imageName = row["人员姓名"].ToString() + row["身份证件号码"].ToString();
                var orgImageName = fileNames.FirstOrDefault(_ => _.Contains(imageName));
                var imagePath = USER_PIC_FOLDER_PATH + "\\" + orgImageName;
                if (string.IsNullOrWhiteSpace(orgImageName))    // 1. 根据姓名，身份证号匹配失败，启动姓名匹配
                {
                    // 通过姓名匹配照片
                    imageName = row["人员姓名"].ToString();
                    orgImageName = fileNames.FirstOrDefault(_ => _.Contains(imageName));
                    imagePath = USER_PIC_FOLDER_PATH + "\\" + orgImageName;
                    if (string.IsNullOrWhiteSpace(orgImageName))    // 2. 根据姓名匹配失败，启动身份证号匹配
                    {

                        // 通过身份证号匹配照片
                        imageName = row["身份证件号码"].ToString();
                        orgImageName = fileNames.FirstOrDefault(_ => _.Contains(imageName));
                        imagePath = USER_PIC_FOLDER_PATH + "\\" + orgImageName;
                        if (string.IsNullOrWhiteSpace(orgImageName))   // 3. 根据身份证号匹配失败
                        {
                            listViewItem.ForeColor = Color.Red;
                            listViewItem.SubItems.Add("-");
                            listViewItem.SubItems.Add("无法匹配照片;");
                            this.imgUserPic.Images.Add(imageList1.Images[0]);
                        }
                        else
                        {
                            // 3. 根据身份证号匹配成
                            listViewItem.ForeColor = Color.Pink;
                            listViewItem.SubItems.Add(orgImageName);
                            string msg = "通过身份证匹配照片，请核实;";
                            var img = GetImageByPath(imagePath);
                            if (img == null)
                            {
                                msg = "头像文件损坏，请更换;";
                                listViewItem.ForeColor = Color.Red;
                                this.imgUserPic.Images.Add(imageList1.Images[0]);
                            }
                            else
                            {
                                CheckWarnPersonCount++;
                                this.imgUserPic.Images.Add(img);
                            }
                            listViewItem.SubItems.Add(msg);
                        }
                    }
                    else
                    {
                        // 2. 根据姓名匹配成功
                        listViewItem.ForeColor = Color.Pink;
                        listViewItem.SubItems.Add(orgImageName);
                        string msg = "通过姓名匹配照片，请核实;";
                        var img = GetImageByPath(imagePath);
                        if (img == null)
                        {
                            msg += "头像文件损坏，请更换;";
                            listViewItem.ForeColor = Color.Red;
                            this.imgUserPic.Images.Add(imageList1.Images[0]);
                        }
                        else
                        {
                            CheckWarnPersonCount++;
                            this.imgUserPic.Images.Add(img);
                        }
                        listViewItem.SubItems.Add(msg);
                    }
                }
                else
                {
                    // 1. 通过姓名身份证号匹配的照片成功
                    listViewItem.SubItems.Add(orgImageName);
                    var img = GetImageByPath(imagePath);
                    string msg = "";
                    if (img == null)
                    {
                        msg = "头像文件损坏，请更换;";
                        listViewItem.ForeColor = Color.Red;
                        this.imgUserPic.Images.Add(imageList1.Images[0]);
                    }
                    else
                    {
                        this.imgUserPic.Images.Add(img);
                    }
                    listViewItem.SubItems.Add(msg);
                }
                // 无法匹配照片
                if (listViewItem.ForeColor != Color.Red)
                {
                    // 照片匹配成功，检测照片合法性
                    var file = files.FirstOrDefault(_ => Path.GetFileName(_.FullName) == orgImageName);
                    if (files != null)
                    {
                        var sizeOfMB = Math.Ceiling(file.Length / 1024.0 / 1024.0);
                        if (sizeOfMB > 1.8)
                        {
                            listViewItem.SubItems.Add("头像最大不能超过1.8兆;");
                            listViewItem.ForeColor = Color.Red;
                        }
                        else
                        {
                            var check34 = CheckUserPic(file.FullName);
                            if (check34 == -1)
                            {
                                listViewItem.SubItems[4].Text = "头像文件损坏，请更换;";
                                listViewItem.ForeColor = Color.Red;
                            }
                            else
                            {
                                if (check34 != 1)
                                {
                                    listViewItem.SubItems[4].Text = "头像文件不符合3:4比例，请更换;";
                                    listViewItem.ForeColor = Color.Red;
                                    this.imgUserPic.Images.Add(imageList1.Images[0]);
                                }
                            }
                        }
                    }
                }
                this.lvwUsers.Items.Add(listViewItem);
            }
            this.lvwUsers.EndUpdate();
            return true;
        }

        private Image GetImageByPath(string path)
        {
            try
            {
                var img = Image.FromFile(path);
                var bmp = new Bitmap(img);
                img.Dispose();
                return bmp;
            }
            catch (Exception)
            {
                return null;
            }
        }
        /// <summary>
        /// 检查图片是否合法
        /// </summary>
        /// <param name="filePath">图片路径</param>
        /// <returns></returns>
        private int CheckUserPic(string filePath)
        {
            try
            {
                using (var memoryStream = new MemoryStream(File.ReadAllBytes(filePath)))
                {
                    using (var image = Image.FromStream(memoryStream))
                    {
                        double ratio = (double)image.Width / (double)image.Height;
                        //if (image.Width * image.HorizontalResolution < 24000)
                        //{
                        //    this.txtPicCheckMessage.AppendText("|  [" + Path.GetFileName(filePath) + "] 不满足最小参数要求（需大于240*320 100dpi）" + Environment.NewLine);
                        //}

                        if (ratio >= 0.60 && ratio <= 0.88)
                        {
                            return 1;
                        }
                    }
                }
            }
            catch (Exception)
            {
                return -1;
            }
            return 0;
        }
        private string SelectedFolderPath()
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择导出文件路径";

            if (dialog.ShowDialog() == DialogResult.OK)
            {
                return dialog.SelectedPath;
            }
            return string.Empty;
        }
        public void DeleteFolder(string dir)
        {
            if (Directory.Exists(dir)) //如果存在这个文件夹删除之
            {
                foreach (string d in Directory.GetFileSystemEntries(dir))
                {
                    if (File.Exists(d))
                        File.Delete(d); //直接删除其中的文件
                    else
                        DeleteFolder(d); //递归删除子文件夹
                }
                Directory.Delete(dir, true); //删除已空文件夹
            }
        }


        private void Export1(string strSelectedPath, DataTable dt2)
        {



            ApplicationFormParam param = new ApplicationFormParam()
            {
                Title = "境内人员证件申领表",
                FileFullPath = strSelectedPath + "\\境内人员信息汇总表" + DateTime.Now.ToString("yyyMMddHHmm") + ".xls",
                SheetName = "境内人员",
                dt = dt2
            };
            string str = strSelectedPath + "\\" + Group + "_" + Create(param) + ".pdf";
            OfficeToPdf.ConverterToPdf(param.FileFullPath, str);
            File.Delete(param.FileFullPath); 
        }
        private DataTable antlistToDataTable(AntList<DomesticAttendeeForTable> domesticAttendees)
        {

            return dt;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            var picFolderPath = USER_PIC_FOLDER_PATH;

            if (!Directory.Exists(picFolderPath))
            {
                AntdUI.Message.warn(window, "【" + picFolderPath + "】不存在,请重新选择人员头像目录", autoClose: 1);
                return;
            }
            if (domesticAttendees.Count == 0)
            {
                AntdUI.Message.warn(window, "境内人员中没有数据，请添加或者导入", autoClose: 1);
                return;
            }
            if (!CheckIfExitRegularData())
            {
                AntdUI.Message.warn(window, "【" + picFolderPath + "】中没有符合要求的图片数据，请重新添加或者导入", autoClose: 1);
                return;
            }

            var result = AntdUI. Modal.open(window, "导出提示！", "该操作只会导出匹配成功的数据，是否继续导出？", TType.Warn);
            
            if (result == DialogResult.No)
            {
                return;
            }

            string strSelectedPath = SelectedFolderPath();

            if (string.IsNullOrWhiteSpace(strSelectedPath))
            {
                return;
            }
            DataTable dt2 = dt.Clone();
            var query = dt.AsEnumerable().Where<DataRow>(a => list.Contains(a["身份证件号码"].ToString()));
            foreach (DataRow item in query)
            {
                dt2.Rows.Add(item.ItemArray);
            }

            Export1(strSelectedPath, dt2);
            InitZip(strSelectedPath, dt2);
            AntdUI.Message.success(window, "文件导出成功", autoClose: 1);
        }

        private bool CheckIfExitRegularData()
        {
            foreach (ListViewItem item in this.lvwUsers.Items)
            {
                if (item.ForeColor != Color.Red)
                {
                    list.Add(item.SubItems[2].Text);
                }
            }
            if (list.Count > 0)
                return true;
            return false;
        }
        private void InitZip(string strSelectedPath, DataTable dt2)
        {
            this.progress.Value = 10;
            // create export folder
            var zipDir = strSelectedPath + "\\境内人员" + DateTime.Now.ToString("yyyMMdd");
            if (!Directory.Exists(zipDir)) Directory.CreateDirectory(zipDir);

            var userPicDir = zipDir + "\\照片";
            if (!Directory.Exists(userPicDir)) Directory.CreateDirectory(userPicDir);
            // Copy  user profile picture to export folder
            this.progress.Value = 12;
            //tupian
            foreach (ListViewItem row in lvwUsers.Items)
            {
                if(row.ForeColor == Color.Red) { continue; }
                var orgFullName = USER_PIC_FOLDER_PATH + "\\" + row.SubItems[3].Text;
                var nameFieldValue = row.SubItems[1].Text.ToString();
                var idNumberFieldValue = row.SubItems[2].Text.ToString();
                string picName = nameFieldValue + idNumberFieldValue + Path.GetExtension(orgFullName);
                var tarFullName = userPicDir + "\\" + picName;
                this.lblLiveMessage.Text = "正在拷贝-" + orgFullName;
                this.lblLiveMessage.Refresh();
                File.Copy(orgFullName, tarFullName, true);
            }
            var dtApplication = new DataTable("cart");
            dtApplication.Columns.Add(new DataColumn("序号", Type.GetType("System.String")));

            dtApplication.Columns.Add(new DataColumn("批次号", Type.GetType("System.String")));

            dtApplication.Columns.Add(new DataColumn("姓名", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("出生日期", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("性别", Type.GetType("System.String")));

            dtApplication.Columns.Add(new DataColumn("户籍所在省/自治区/直辖市", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("户籍所在城市", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("户籍所在地", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("当前居住省", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("当前居住城市", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("当前居住地", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("身份证件类型", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("身份证件号码", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("证件类别", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("持证人身份类别", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("通行区域", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("工作单位", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("负责单位", Type.GetType("System.String")));

            dtApplication.Columns.Add(new DataColumn("移动电话", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("备注", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("审查证件号码", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("人员照片", Type.GetType("System.String")));


            dtApplication.Columns.Add(new DataColumn("重大活动名称", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("进馆开始日期", Type.GetType("System.String")));
            dtApplication.Columns.Add(new DataColumn("进馆结束日期", Type.GetType("System.String"))); 
             

            for (int i = 0; i < dt2.Rows.Count; i++)
            {
              
                DataRow dr = dtApplication.NewRow();
                string identityCard = dt2.Rows[i]["身份证件号码"].ToString();
                if (identityCard.Length == 18)//处理18位的身份证号码从号码中得到生日和性别代码
                {
                    dr["出生日期"] = identityCard.Substring(6, 4) + "-" + identityCard.Substring(10, 2) + "-" + identityCard.Substring(12, 2);
                    
                }
                if (identityCard.Length == 15)
                {
                    dr["出生日期"] = "19" + identityCard.Substring(6, 2) + "-" + identityCard.Substring(8, 2) + "-" + identityCard.Substring(10, 2);
                  
                }
           
                dr["序号"] = (i + 1);
                dr["姓名"] = dt2.Rows[i]["人员姓名"];
                dr["身份证件类型"] = "C_ID";
                dr["证件类别"] = dt2.Rows[i]["办证类型"];
                dr["工作单位"] = dt2.Rows[i]["工作单位"];
                dr["备注"] = "";
                dtApplication.Rows.Add(dr);
            }
            // create excel data file 
            this.progress.Value = 20;
            var xlsName = "境内人员" + DateTime.Now.ToString("yyyMMdd") + ".xls";
            this.lblLiveMessage.Text = "生成-" + xlsName;
            this.lblLiveMessage.Refresh();
            var stream = DataTableToExcelXls(dtApplication, "境内人员");
            byte[] bytes = new byte[stream.Length];
            stream.Read(bytes, 0, bytes.Length);
            stream.Seek(0, SeekOrigin.Begin);

            FileStream fs = new FileStream(zipDir + "\\" + xlsName, FileMode.Create);
            BinaryWriter bw = new BinaryWriter(fs);
            bw.Write(bytes);
            bw.Close();
            fs.Close();
            var zipPath = strSelectedPath + "\\" + "境内人员" + DateTime.Now.ToString("yyyMMddHHmm") + ".fhp";

            ZipUtility.Create(zipDir, zipPath, ConfigurationManager.AppSettings["sheetPasse"].ToString());
            this.lblLiveMessage.Text = "已保存至[" + zipPath + "]";
            this.progress.Value = 70;
            DeleteFolder(zipDir);
            this.progress.Value = 100;
        }
        //private DataTable convertToExportDt(DataTable dt2)
        //{

        //}
        public static string Create(ApplicationFormParam param)
        {
            using (FileStream fs = new FileStream(param.FileFullPath, FileMode.Create, FileAccess.Write))
            {

                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet(param.Title);

                var passe = ConfigurationManager.AppSettings["sheetPasse"].ToString();
                if (!string.IsNullOrWhiteSpace(passe)) sheet.ProtectSheet(passe);

                // title
                var titleStyle = workbook.CreateCellStyle();
                // 设置居中
                titleStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                var fontStyle = workbook.CreateFont();
                fontStyle.IsBold = true;
                fontStyle.FontName = "新宋体";
                fontStyle.FontHeightInPoints = 15;
                titleStyle.SetFont(fontStyle);
                titleStyle.WrapText = true;

                var titleRow = sheet.CreateRow(0);
                titleRow.Height = 40 * 20;
                var cellTitle = titleRow.CreateCell(0);
                cellTitle.SetCellValue(param.Title);
                cellTitle.CellStyle = titleStyle;
                //二维码
                string twoPic = Guid.NewGuid().ToString();
                byte[] bytes = QRcode(twoPic, "10", "10");
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG);
                /*插图片的位置HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 
                 * 前面四个表示在单元格中两个点的位置，后面四个表示是哪个单元格
                 * 图片左上角为(col1, row1)第row+1行col+1列，
                 * 右下角为(col2, row2)第col2+1行row2+1列，
                 * dx2控制图片高度，dy2控制图片宽度。
                 * dx2最大值1023，dy2最大值255
                 */
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 767, 255, 5, 0, 5, 1);
                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

                // 设置字体
                var font = workbook.CreateFont();
                font.FontName = "新宋体";
                font.FontHeightInPoints = 11;

                string checkNum = EightCheck();
                var applicationDateRow = sheet.CreateRow(1);
                applicationDateRow.Height = 20 * 20;
                applicationDateRow.CreateCell(0).SetCellValue("组别：" + Group + " 校验码：" + checkNum);
            
                var borderStyle = workbook.CreateCellStyle();
                borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.TopBorderColor = HSSFColor.Black.Index;
                borderStyle.RightBorderColor = HSSFColor.Black.Index;
                borderStyle.BottomBorderColor = HSSFColor.Black.Index;
                borderStyle.LeftBorderColor = HSSFColor.Black.Index;
                borderStyle.SetFont(font);
                borderStyle.WrapText = true;

                var headerDateRow = sheet.CreateRow(2);
                for (int i = 0; i < param.dt.Columns.Count; i++)
                {
                    var strColumnName = param.dt.Columns[i].ColumnName;
                    var cell = headerDateRow.CreateCell(i);
                    cell.CellStyle = borderStyle;
                    cell.SetCellValue(strColumnName);
                    int length = 0;
                    if (strColumnName != "序号")
                    {
                        length = Encoding.Default.GetBytes(strColumnName).Length + 10;
                    }
                    else
                    {
                        length = 6;
                    }
                    sheet.SetColumnWidth(i, length * 256);
                }
                var rowIndex = 3;
                for (int i = 0; i < param.dt.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(rowIndex);
                   
                    for (int j = 0; j < param.dt.Columns.Count; j++)
                    {
                        var strColumnName = param.dt.Columns[j].ColumnName;
                        var rowValue = param.dt.Rows[i][param.dt.Columns[j].ColumnName].ToString();
                        var cell = row.CreateCell(j);
                        cell.CellStyle = borderStyle;
                        if (strColumnName == "序号")
                        {
                            cell.SetCellValue((i + 1).ToString());
                        }
                        else
                        {
                            cell.SetCellValue(rowValue);
                        }
                        //var columnWidth = sheet.GetColumnWidth(i);
                        //int length = Encoding.Default.GetBytes(rowValue).Length + 2;
                        //var newColumnWidth = length * 256;
                        //if (newColumnWidth > columnWidth) sheet.SetColumnWidth(i, newColumnWidth);
                    }
                    rowIndex++;
                }

                var shenStyle = workbook.CreateCellStyle();
                shenStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                shenStyle.TopBorderColor = HSSFColor.Black.Index;
                shenStyle.RightBorderColor = HSSFColor.Black.Index;
                shenStyle.BottomBorderColor = HSSFColor.Black.Index;
                shenStyle.LeftBorderColor = HSSFColor.Black.Index;
                shenStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                shenStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Justify;

                for (int i = rowIndex; i < rowIndex + 6; i++)
                {
                    var row = sheet.CreateRow(i);
                    for (int j = 0; j < 6; j++)
                    {
                        var cell = row.CreateCell(j);
                        if (j == 0)
                        {
                            cell.SetCellValue((" 审\n 批\n 人\n 意\n 见"));
                        }
                        cell.CellStyle = shenStyle;
                    }
                }


                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 5, 0, 0));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 5, 1, 5));



                var cardAdminRow = sheet.CreateRow(rowIndex + 7);
                cardAdminRow.CreateCell(0).SetCellValue("办理人：");
                cardAdminRow.CreateCell(2).SetCellValue("电  话：");
                cardAdminRow.CreateCell(4).SetCellValue("办理时间：");
                var unitPrincipalRow = sheet.CreateRow(rowIndex + 9);
                unitPrincipalRow.CreateCell(0).SetCellValue("领办人：");
                unitPrincipalRow.CreateCell(2).SetCellValue("电  话：");
                unitPrincipalRow.CreateCell(4).SetCellValue("领取时间：");
                workbook.Write(fs);
                fs.Flush(true);
                return checkNum;
            }

        }


        private static string EightCheck()
        {
            int ran;
            byte[] r = new byte[8];
            Random rand = new Random((int)(DateTime.Now.Ticks % 1000000));
            //生成8字节原始数据
            for (int i = 0; i < 8; i++)
                //while循环剔除非字母和数字的随机数
                do
                {
                    //数字范围是ASCII码中字母数字和一些符号
                    ran = rand.Next(48, 122);
                    r[i] = Convert.ToByte(ran);
                } while ((ran >= 58 && ran <= 64) || (ran >= 91 && ran <= 96));
            //转换成8位String类型               
          
            return Encoding.ASCII.GetString(r).ToUpper();
        }
        private static byte[] QRcode(string text, string width, string height)
        {

            try
            {
                BarcodeWriter writer = new BarcodeWriter();
                writer.Format = BarcodeFormat.QR_CODE;
                QrCodeEncodingOptions options = new QrCodeEncodingOptions();
                options.DisableECI = true;
                //设置内容编码
                options.CharacterSet = "UTF-8";
                //将传来的值赋给二维码的宽度和高度
                options.Width = Convert.ToInt32(width);
                options.Height = Convert.ToInt32(height);
                //设置二维码的边距,单位不是固定像素
                options.Margin = 1;
                writer.Options = options;

                Bitmap map = writer.Write(text);
                return BitmapToByte(map);

                //二维码会显示在桌面(你也想显示在桌面的话,要改一下路径)

            }
            catch (Exception)
            {
                return null;
            }

        }
        private static byte[] BitmapToByte(System.Drawing.Bitmap bitmap)
        {
            // 1.先将BitMap转成内存流
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
            ms.Seek(0, System.IO.SeekOrigin.Begin);
            // 2.再将内存流转成byte[]并返回
            byte[] bytes = new byte[ms.Length];
            ms.Read(bytes, 0, bytes.Length);
            ms.Dispose();
            return bytes;
        }
        public static Stream DataTableToExcelXls(DataTable dataTable, string sheetName)
        {
            try
            {
                const int startIndex = 0;
                var fields = dataTable.Columns;
                //创建excel数据结构
                var workbook = new HSSFWorkbook();
                //创建excel工作表
                var sheet = workbook.CreateSheet(sheetName);
                var borderStyle = workbook.CreateCellStyle();
                borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.TopBorderColor = HSSFColor.Black.Index;
                borderStyle.RightBorderColor = HSSFColor.Black.Index;
                borderStyle.BottomBorderColor = HSSFColor.Black.Index;
                borderStyle.LeftBorderColor = HSSFColor.Black.Index;
            
                borderStyle.WrapText = true;
                var row = sheet.CreateRow(startIndex);
                for(int i = 0;i<dataTable.Columns.Count;i++)
                { 
                     DataColumn column = dataTable.Columns[i];
                    string strColumnName = column.ColumnName;
                    var cellIndex = fields.IndexOf(column) + startIndex;
                    var cell = row.CreateCell(cellIndex);
                    cell.CellStyle = borderStyle;
                    cell.SetCellValue(column.ColumnName);
                    sheet.AutoSizeColumn(cellIndex);
                    int length = 0;
                    if (strColumnName != "序号")
                    {
                        length = Encoding.Default.GetBytes(strColumnName).Length + 10;
                    }
                    else
                    {
                        length = 6;
                    }
                    sheet.SetColumnWidth(i, length * 256);
                }

                int rowIndex = 0;
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    row = sheet.CreateRow(rowIndex + 1);
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        var cellIndex = fields.IndexOf(column) + startIndex;
                        var cell = row.CreateCell(cellIndex);
                        var value = dataRow[column.ColumnName];
                        switch ((value ?? string.Empty).GetType().Name.ToLower())
                        {
                            case "int32":
                            case "int64":
                            case "decimal":
                                cell.SetCellValue(Convert.ToDouble(value));
                                break;
                            default:
                                cell.SetCellValue(Convert.ToString(value));
                                break;
                        }
                    }
                    rowIndex++;
                }

                MemoryStream ms = new MemoryStream();
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;//指定当前流的位置从0开始

                workbook = null;
                sheet = null;
                row = null;

                return ms;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
 
    }
}
